<?php
class Employed
{		
	
	public function __construct ()
	{
		
		
	}
	
	/**
	*	Funcion responsable de agregar el formulario de Client Information Form
	*	@autor: JJR
	*	@date: 01/26/2012
	*	@return:
	*/
	
	function addEmployed($data){
		$sql = "INSERT INTO hdv (
		hdv_code ,
		hdv_name ,
		hdv_lastname ,
		hdv_cedula ,
		hdv_address ,
		hdv_city ,
		state_code ,
		hdv_birth , 
		hdv_funeral ,
		eps_code ,
		arp_code ,
		afp_code ,
		hdv_name_poliza ,
		hdv_value_poliza ,
		hdv_name_seguro_vida ,
		hdv_value_seguro_vida ,
		hdv_value_otros ,
		hdv_name_otros ,
		hdv_email ,
		ase_code ,
		hdv_contratado ,
		hdv_date_create ,
		hdv_status)
		VALUES (
		NULL ,
		'".$data->hdv_name."',
		'".$data->hdv_lastname."',
		'".$data->hdv_cedula."',
		'".$data->hdv_address."',
		'".$data->hdv_city."',
		'".$data->state_code."',
		'".$data->hdv_birth."',
		'".$data->hdv_funeral."',
		'".$data->eps_code."',
		'".$data->arp_code."',
		'".$data->afp_code."',
		'".$data->hdv_name_poliza."',
		'".$data->hdv_value_poliza."',
		'".$data->hdv_name_seguro_vida."',
		'".$data->hdv_value_seguro_vida."',
		'".$data->hdv_value_otros."',
		'".$data->hdv_name_otros."',
		'".$data->hdv_email."',
		'".$data->ase_code."',
		'".$data->hdv_contratado."',
		'".date("Y-m-d H:i:s")."',
		'Active');";	


		if(mysql_query($sql)){		
			return mysql_insert_id ();
		}else
			return false;
	}
	
	/**
	*	Esta funcion es responsable de insertar datos en la tabla case que son los campos del tipo de negocio
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@parameters: 
	*	@return:
	*/
	function addBusiness($data){
		$sql = "INSERT INTO my_case (
		case_code,
		case_number,
		pro_comment,
		pro_date_create,
		cli_code,
		bus_code,
		use_code,
		pro_status)
		VALUES (
		NULL,
		'".$data->case_number."',
		'".$data->pro_comment."',
		'".date("Y-m-d H:i:s")."',
		'".$data->cli_code."',
		'".$data->bus_code."',
		'".$data->use_code."'
		'Active');"; 
		
		return mysql_query ($sql);
	}
	
	/**
	*	Esta funcion es la encargada de listar los clientes que existen en el sitio.
	*	@autor: JJR
	*	@date: 01/25/2012
	*	@parameters: 
	*	@return:
	*/
	function employedList($search='',$order_by='', $inicio = 0,$cantidad = 0){
		$where = '';		
		if($search != '')
		$where = " AND (hdv_cedula like '%".$search."%'
				   OR hdv_name like '%".$search."%'
				   OR hdv_lastname like '%".$search."%'
				   OR hdvc_address like '%".$search."%' 		           
				   OR hdvc_city like '%".$search."%' 
				   OR state_code like '%".$search."%')";
		
		if ($order_by == '')
			$order_by = ' ORDER BY hdv_name ';
		

		$sql = "
		SELECT t4.hdv_code, t4.hdv_cedula, t4.hdv_name, t4.hdv_lastname, t4.hdv_address, t4.hdv_city, t4.state_code, st.state_name FROM
		(SELECT c.hdv_code, c.hdv_cedula, c.hdv_name, c.hdv_lastname, c.hdv_address, c.hdv_city, c.state_code
			FROM hdv c WHERE c.hdv_status<> 'Deleted' ".$where." ".$order_by.") t4 
		LEFT JOIN state st ON t4.state_code=st.state_code";
		
		if ( !($inicio == 0 && $cantidad == 0 ) )
			$sql .= ' LIMIT '.$inicio.', '.$cantidad.';';
		
		$consult  = mysql_query($sql);
		$data;
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			
			$data[$i]->hdv_code= $row['hdv_code'];
			$data[$i]->hdv_cedula= $row['hdv_cedula'];
			$data[$i]->hdv_name= $row['hdv_name'];
			$data[$i]->hdv_lastname= $row['hdv_lastname'];
			$data[$i]->hdv_address = $row['hdv_address'];
			$data[$i]->hdv_city= $row['hdv_city'];	
			$data[$i]->state_code= $row['state_code'];
			$data[$i]->state_name= $row['state_name'];
			$data[$i]->telephones = $this->getTelephones((int) $row['hdv_code']);

			$i++;				
		}
		return $data;
	}	

	
	/**
	*	Esta funcion es la encargada de listar los business de los clientes que existen en el sitio.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@parameters: 
	*	@return:
	*/
	function businessList($search='',$order_by='', $inicio = 0,$cantidad = 0){
		$where = '';		
		if($search != '')
		$where = "  WHERE (	           
				    cli_name like '%".$search."%'
				   OR cli_lastname like '%".$search."%'
				   OR cli_ss like '%".$search."%'
				   OR use_name like '%".$search."%'
				   OR use_lastname like '%".$search."%'
				   OR pro_comment like '%".$search."%'
				   OR pro_date_create like '%".$search."%')";
		
		if ($order_by == '')
			$order_by = ' ORDER BY pro_date_create DESC ';
		

			$sql = "SELECT t6.*, u.use_name, u.use_lastname FROM
			(SELECT t5.*, p.bus_name FROM
			(SELECT t4.*, c.cli_name, c.cli_lastname,c.cli_ss FROM
			(SELECT case_code,case_number,pro_comment,pro_date_create,cli_code,bus_code,use_code FROM my_case WHERE pro_status<>'Deleted') 
			t4 LEFT JOIN client c ON t4.cli_code=c.cli_code)
			t5 LEFT JOIN business_type p ON t5.bus_code=p.bus_code)
			t6 LEFT JOIN user u ON t6.use_code=u.use_code ".$where." ".$order_by.""; 
			
			
		
		if ( !($inicio == 0 && $cantidad == 0 ) )
			$sql .= ' LIMIT '.$inicio.', '.$cantidad.';';
		
		$consult  = mysql_query($sql);
		$data;
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			$data[$i]->case_code= $row['case_code'];
			$data[$i]->cli_code= $row['cli_code'];
			$data[$i]->bus_code = $row['bus_code'];
			$data[$i]->bus_name = $row['bus_name'];
			$data[$i]->pro_comment= $row['pro_comment'];
			$data[$i]->pro_date_create= $row['pro_date_create'];
			$data[$i]->use_code= $row['use_code'];
			$data[$i]->cli_name= $row['cli_name'];
			$data[$i]->cli_lastname= $row['cli_lastname'];
			$data[$i]->cli_ss= $row['cli_ss'];
			$data[$i]->use_name= $row['use_name'];
			$data[$i]->use_lastname= $row['use_lastname'];

			$i++;				
		}
		return $data;
	}	
	
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/28/2012
	*	Funcion encargada de contar la cantidad de business.
	*	@return:
	*/

	function countBusiness(){		
			
		$sql = "SELECT COUNT(*) as cant FROM my_case ";
	
		$consulta = mysql_query($sql);
		$row = mysql_fetch_array($consulta);
			
		return $row['cant'];
	}	
			
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de contar la cantidad de clientes en la aplicacion.
	*	@return:
	*/
	function countEmployed($search){
		$where = '';		
		if($search != '')
		$where = " AND (hdv_cedula like '%".$search."%'
				   OR hdv_name like '%".$search."%'
				   OR hdv_lastname like '%".$search."%'
				   OR hdv_address like '%".$search."%' 		           
				   OR hdv_city like '%".$search."%' 
				   OR state_code like '%".$search."%')";
		
		$sql = "SELECT COUNT(*) AS cant FROM (SELECT c.hdv_code, c.hdv_cedula, c.hdv_name, c.hdv_lastname, c.hdv_address, c.hdv_city,state_code
			FROM hdv c WHERE c.hdv_status<> 'Deleted' ".$where.") t4 LEFT JOIN state st ON t4.state_code=st.state_code";
		
		$consulta = mysql_query($sql);
		$row = mysql_fetch_array($consulta);
			
		return $row['cant'];
	}	
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de listar los estados.
	*	@return:
	*/
	function stateList()
	{
		$sql = "SELECT state_code, state_name FROM state";
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->state_code 		= $row["state_code"];
			$data[$i]->state_name 		= $row["state_name"];	
						
			$i++;
		}			
		return $data;
	}	
	

	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de listar los users.
	*	@return:
	*/
	function userList()
	{
		$sql = "SELECT use_code, use_name,use_lastname FROM user";
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->use_code 		= $row["use_code"];
			$data[$i]->use_name 		= $row["use_name"];	
			$data[$i]->use_lastname 	= $row["use_lastname"];
						
			$i++;
		}			
		return $data;
	}	
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de listar los Business Types.
	*	@return:
	*/
	function businessTypeList()
	{
		$sql = "SELECT bus_code, bus_name FROM business_type"; 
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->bus_code 		= $row["bus_code"];
			$data[$i]->bus_name 		= $row["bus_name"];	

			$i++;
		}			
		return $data;
	}	
	
	/**
	*	Funcion responsable de agregar el nombre del business Type.
	*	@autor: JJR
	*	@date: 02/02/2012
	*	@return:
	*/	
	function addBusinessType($data){
		$sql = "INSERT INTO business_type ( bus_code, bus_name) VALUES (NULL, '".$data->bus_name."');";	

		if(mysql_query($sql)){		
			return mysql_insert_id ();
		}else
			return false;
	}	
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/28/2012
	*	Funcion encargada de listar los case number o Title name del Negocio.
	*	@return:
	*/
	function caseNumberList()
	{
		$sql = "SELECT case_code, case_number FROM my_case"; 
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->case_code 		= $row["case_code"];
			$data[$i]->case_number 		= $row["case_number"];	

			$i++;
		}			
		return $data;
	}		
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/26/2012
	*	Funcion encargada de listar los Business Types.
	*	@return:
	*/
	function clientNameList()
	{
		$sql = "SELECT cli_code, cli_name, cli_lastname FROM client"; 
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult))
		{
			$data[$i]->cli_code 		= $row["cli_code"];
			$data[$i]->cli_name 		= $row["cli_name"];	
			$data[$i]->cli_lastname 	= $row["cli_lastname"];	
			$i++;
		}			
		return $data;
	}	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/27/2012
	*	Funcion encargada de obtener los datos de un cliente.
	*	@return:
	*/	
	
	function getEmployedData($id)
	{
		 $sql = "SELECT c.hdv_code, c.hdv_name, c.hdv_lastname, c.hdv_cedula, c.hdv_address, c.hdv_city, c.state_code, c.hdv_birth, c.hdv_email
		FROM hdv c WHERE c.hdv_status<> 'Deleted' AND c.hdv_code = ".$id;
		$consult = mysql_query($sql);
		$row = mysql_fetch_array($consult);
		
		$data->hdv_code   				= $row['hdv_code'] ;
		$data->hdv_name   				= $row['hdv_name'] ;
		$data->hdv_lastname   			= $row['hdv_lastname'] ;
		$data->hdv_cedula   			= $row['hdv_cedula'] ;
		$data->hdv_address  			= $row['hdv_address'] ;
		$data->hdv_city 				= $row['hdv_city'] ;
		$data->state_code 				= $row['state_code'] ;
		$data->hdv_birth 				= $row['hdv_birth'] ;
		$data->hdv_email 				= $row['hdv_email'] ;
		
		return $data;	
	}
	

	/**
	*	@autor: JJR
	*	@date: 01/28/2012
	*	Funcion encargada de Actualizar los clientes.
	*	@return:
	*/
	function updateEmployed($new_data){	
		$sql="UPDATE hdv SET 
		hdv_name = '".$new_data->hdv_name."',
		hdv_lastname = '".$new_data->hdv_lastname."',
		hdv_cedula = '".$new_data->hdv_cedula."',
		hdv_address = '".$new_data->hdv_address."',
		hdv_city = '".$new_data->hdv_city."',
		state_code = '".$new_data->state_code."',
		hdv_birth = '".$new_data->hdv_birth."',
		hdv_email = '".$new_data->hdv_email."',
		hdv_contratado = '".$new_data->hdv_contratado."'
		WHERE hdv_code =".$new_data->hdv_code.";"; 
		
	
		if(mysql_query($sql)){		
			return true;
		}else
			return false;
	}			
	
	/**
	*	Funcion encargada de Actualizar los datos de un cliente.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@parameters: 
	*	@return:
	*/	
		function deleteEmployed($hdv_code)
	{
		$sql="UPDATE `hdv` SET `hdv_status` =  'Deleted' WHERE `hdv_code` =".$hdv_code;
		return mysql_query($sql);
	}

	/**
	*	
	*	@autor: JJR
	*	@date: 01/27/2012
	*	Funcion encargada de obtener los datos de un cliente.
	*	@return:
	*/	
	
	function getBusinessData($case_code)
	{
		 $sql = "SELECT p.case_code, p.case_number, p.pro_comment, p.cli_code, p.bus_code, p.use_code
		FROM my_case p WHERE p.case_code = ".$case_code;
		$consult = mysql_query($sql);
		$row = mysql_fetch_array($consult);
		
		$data->case_code   				= $row['case_code'] ;
		$data->case_number   				= $row['case_number'] ;
		$data->pro_comment   			= $row['pro_comment'] ;
		$data->cli_code   				= $row['cli_code'] ;
		$data->bus_code  				= $row['bus_code'] ;
		$data->use_code 				= $row['use_code'] ;
		
		
		return $data;	
	}	
	
	/**
	*	
	*	@autor: JJR
	*	@date: 01/30/2012
	*	Funcion encargada de Actualizar los Business.
	*	@return:
	*/	
	
		function updateBusiness($new_data){
	
		$sql="UPDATE case SET 
		case_number     = '".$new_data->case_number."',
		pro_comment    = '".$new_data->pro_comment."',
		cli_code    = '".$new_data->cli_code."',
		bus_code    = '".$new_data->bus_code."',
		use_code    = '".$new_data->use_code."'
		WHERE case_code =".$new_data->case_code.";";  
			
		if(mysql_query($sql)){		
			return mysql_insert_id ();
		}else
			return false;
	}	

	/**
	*	Funcion respponsable de agregar un telefono para un cliente;
	*	@autor: JSL
	*	@date: 29/01/2012
	*/
	function addTelephone($data){
		$sql = "INSERT INTO telephone (tel_code, tel_number, tel_ext, tel_type, id_fk) VALUES (NULL, '".$data->tel_number."', '".$data->tel_ext."', 'Employed', '".$data->id_fk."');";
		
		if( mysql_query ($sql) ) 
			return true;	
		else
			return false;
	}
	
	/**
	*	Funcion responsable de listar el/los telefonos de un clientes.
	*	@autor: JSL
	*	@date: 30/01/2012
	*/
	function getTelephones($id_fk){
		$sql = "SELECT t.tel_code, t.tel_number, t.tel_ext FROM telephone t WHERE t.tel_type='Employed' AND t.id_fk=".$id_fk." ORDER BY tel_code;";
		$data = array ();
		if ( $result = mysql_query ($sql) ){
			$i = 0;
			while ( $row = mysql_fetch_array($result) ){
				$data[$i]->tel_code = $row["tel_code"];
				$data[$i]->tel_number = $row["tel_number"];
				$data[$i]->tel_ext = $row["tel_ext"];
				
				$i++;
			}
		}
				
		return $data;
	}
	
	/**
	*	Funcion responsable eliminar los telefonos de un cliente.
	*	@autor: JSL
	*	@date: 30/01/2012
	*/
	function deleteTelephones($id_fk){
		$sql = "DELETE FROM telephone  WHERE tel_type='Employed' AND id_fk=".$id_fk.";";
		return mysql_query ($sql);
	}
	
	/**
	*	Funcion encargada de Actualizar el status de un business a deleted
	*	@autor: JJR
	*	@date: 01/30/2012
	*	@parameters: 
	*	@return:
	*/	
		function deleteBusiness($case_code)
	{
		$sql="UPDATE my_case SET `case_status` =  'Deleted' WHERE `case_code` =".$case_code;
		return mysql_query($sql);
	}
	
	/**
	*	Esta funcion es la encargada de listar los clientes por advisers.
	*	@autor: JJR
	*	@date: 02/09/2012
	*	@parameters: 
	*	@return:
	*/
	function reportClientList($use_code){

		$sql = " SELECT t2.case_code,t2.cli_code,t2.use_code,t2.use_name,t2.use_lastname,t2.cli_name, t2.cli_lastname,t2.cli_address,t2.cli_city,t2.state_code,t2.cli_zip_code,t2.cli_status,st.state_name FROM
		(SELECT t1.case_code,t1.cli_code,t1.use_code,t1.use_name,t1.use_lastname,cl.cli_name, cl.cli_lastname,cl.cli_address,cl.cli_city,cl.state_code,cl.cli_zip_code,cl.cli_status FROM
		(SELECT mc.case_code, mc.cli_code, mc.use_code, u.use_name, u.use_lastname FROM my_case mc LEFT JOIN user u ON u.use_code=mc.use_code)t1 LEFT JOIN client cl ON cl.cli_code = t1.cli_code WHERE cl.cli_status = 'Active')t2 LEFT JOIN state st ON st.state_code=t2.state_code WHERE use_code=".$use_code;
		
		$consult  = mysql_query($sql);
		$data;
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			$data[$i]->cli_code= $row['cli_code'];
			$data[$i]->use_code= $row['use_code'];
			$data[$i]->use_name = $row['use_name'];
			$data[$i]->use_lastname= $row['use_lastname'];
			$data[$i]->cli_name= $row['cli_name'];
			$data[$i]->cli_lastname= $row['cli_lastname'];
			$data[$i]->cli_address= $row['cli_address'];	
			$data[$i]->cli_city= $row['cli_city'];
			$data[$i]->state_code= $row['state_code'];
			$data[$i]->cli_zip_code= $row['cli_zip_code'];
			$data[$i]->state_name= $row['state_name'];
			$data[$i]->telephones = $this->getTelephones((int) $row['cli_code']);

			$i++;				
		}
		return $data;
	}		
	
	/**
	*	Esta funcion es la encargada de listar los loan Numbers de los clientes.
	*	@autor: JJR
	*	@date: 02/13/2012
	*	@parameters: 
	*	@return:
	*/
	function loanNumberList(){

		$sql = " SELECT t0.case_code, t0.loan_number, t0.cli_code, cl.cli_name, cl.cli_lastname FROM
		(SELECT re.case_code,re.loan_number,mc.cli_code FROM request re JOIN my_case mc WHERE re.case_code=mc.case_code)t0 LEFT JOIN client cl ON cl.cli_code=t0.cli_code";
		
		
		$consult  = mysql_query($sql);
		$data;
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			$data[$i]->cli_code= $row['cli_code'];
			$data[$i]->use_code= $row['use_code'];
			$data[$i]->use_name = $row['use_name'];
			$data[$i]->use_lastname= $row['use_lastname'];
			$data[$i]->cli_name= $row['cli_name'];
			$data[$i]->cli_lastname= $row['cli_lastname'];
			$data[$i]->cli_address= $row['cli_address'];	
			$data[$i]->cli_city= $row['cli_city'];
			$data[$i]->state_code= $row['state_code'];
			$data[$i]->cli_zip_code= $row['cli_zip_code'];
			$data[$i]->state_name= $row['state_name'];
			$data[$i]->telephones = $this->getTelephones((int) $row['cli_code']);

			$i++;				
		}
		return $data;
	}	
	


}
?>